package com.zhao.fishingguideserver.mapper;

import com.zhao.fishingguideserver.POJO.Post;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PostMapper {

    @Insert("INSERT INTO posts(user_id, title, content, images, category, status, view_count, like_count, comment_count, is_top, is_hot) " +
            "VALUES(#{userId}, #{title}, #{content}, #{images}, #{category}, #{status}, #{viewCount}, #{likeCount}, #{commentCount}, #{isTop}, #{isHot})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int insert(Post post);

    @Update("<script>" +
            "UPDATE posts " +
            "<set>" +
            "  <if test=\"title != null\">title = #{title},</if>" +
            "  <if test=\"content != null\">content = #{content},</if>" +
            "  <if test=\"images != null\">images = #{images},</if>" +
            "  <if test=\"category != null\">category = #{category},</if>" +
            "  <if test=\"status != null\">status = #{status},</if>" +
            "  <if test=\"viewCount != null\">view_count = #{viewCount},</if>" +
            "  <if test=\"likeCount != null\">like_count = #{likeCount},</if>" +
            "  <if test=\"commentCount != null\">comment_count = #{commentCount},</if>" +
            "  <if test=\"isTop != null\">is_top = #{isTop},</if>" +
            "  <if test=\"isHot != null\">is_hot = #{isHot},</if>" +
            "  update_time = now()" +
            "</set>" +
            "WHERE id = #{id}" +
            "</script>")
    int updatePost(Post post);

    @Delete("DELETE FROM posts WHERE id = #{id}")
    int deletePost(Integer id);

    /**
     * 查询所有帖子（管理员用，包括所有状态）
     */
    @Select("SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "ORDER BY p.create_time DESC")
    List<Post> selectAllPosts();
    
    /**
     * 统计帖子数量（管理端，支持状态与关键词）
     */
    @Select("<script>" +
            "SELECT COUNT(1) FROM posts " +
            "<where>" +
            "<if test='status != null and status != \"\"'> AND status = #{status} </if>" +
            "<if test='keyword != null and keyword != \"\"'> AND (title LIKE CONCAT('%', #{keyword}, '%') OR content LIKE CONCAT('%', #{keyword}, '%')) </if>" +
            "</where>" +
            "</script>")
    int countPosts(@Param("status") String status,
                   @Param("keyword") String keyword);
    
    /**
     * 分页查询帖子（管理端，支持状态与关键词，按时间倒序）
     */
    @Select("<script>" +
            "SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "<where>" +
            "<if test='status != null and status != \"\"'> AND p.status = #{status} </if>" +
            "<if test='keyword != null and keyword != \"\"'> AND (p.title LIKE CONCAT('%', #{keyword}, '%') OR p.content LIKE CONCAT('%', #{keyword}, '%')) </if>" +
            "</where>" +
            "ORDER BY p.create_time DESC " +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<Post> selectPostsPaged(@Param("status") String status,
                                @Param("keyword") String keyword,
                                @Param("offset") Integer offset,
                                @Param("limit") Integer limit);

    /**
     * 根据ID查询帖子详情（包含用户信息）
     */
    @Select("SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.id = #{id} AND p.status = 'published'")
    Post selectById(@Param("id") Integer id);

    /**
     * 查询所有已发布的帖子（分页）
     */
    @Select("SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.status = 'published' " +
            "ORDER BY p.is_top DESC, p.is_hot DESC, p.create_time DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Post> selectPublishedPosts(@Param("offset") Integer offset, @Param("limit") Integer limit);

    /**
     * 根据分类查询帖子
     */
    @Select("SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.status = 'published' AND p.category = #{category} " +
            "ORDER BY p.is_top DESC, p.is_hot DESC, p.create_time DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Post> selectByCategory(@Param("category") String category, @Param("offset") Integer offset, @Param("limit") Integer limit);

    /**
     * 根据用户ID查询帖子
     */
    @Select("SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.user_id = #{userId} AND p.status = 'published' " +
            "ORDER BY p.create_time DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Post> selectByUserId(@Param("userId") Integer userId, @Param("offset") Integer offset, @Param("limit") Integer limit);

    /**
     * 搜索帖子（标题和内容）
     */
    @Select("SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.status = 'published' AND (p.title LIKE CONCAT('%', #{keyword}, '%') OR p.content LIKE CONCAT('%', #{keyword}, '%')) " +
            "ORDER BY p.is_top DESC, p.is_hot DESC, p.create_time DESC " +
            "LIMIT #{offset}, #{limit}")
    List<Post> searchPosts(@Param("keyword") String keyword, @Param("offset") Integer offset, @Param("limit") Integer limit);

    /**
     * 查询热门帖子
     */
    @Select("SELECT p.id, p.user_id as userId, p.title, p.content, p.images, p.category, p.status, " +
            "p.view_count as viewCount, p.like_count as likeCount, p.comment_count as commentCount, " +
            "p.is_top as isTop, p.is_hot as isHot, p.create_time as createTime, p.update_time as updateTime, " +
            "u.username, u.nick_name as nickName " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.status = 'published' " +
            "ORDER BY p.like_count DESC, p.comment_count DESC, p.view_count DESC " +
            "LIMIT #{limit}")
    List<Post> selectHotPosts(@Param("limit") Integer limit);

    /**
     * 增加浏览次数
     */
    @Update("UPDATE posts SET view_count = view_count + 1 WHERE id = #{id}")
    int incrementViewCount(@Param("id") Integer id);

    /**
     * 更新点赞数
     */
    @Update("UPDATE posts SET like_count = (SELECT COUNT(*) FROM post_likes WHERE post_id = #{postId}) WHERE id = #{postId}")
    int updateLikeCount(@Param("postId") Integer postId);

    /**
     * 更新评论数
     */
    @Update("UPDATE posts SET comment_count = (SELECT COUNT(*) FROM comments WHERE post_id = #{postId} AND status = 'published') WHERE id = #{postId}")
    int updateCommentCount(@Param("postId") Integer postId);

    /**
     * 统计帖子总数
     */
    @Select("SELECT COUNT(*) FROM posts WHERE status = 'published'")
    int countPublishedPosts();

    /**
     * 根据分类统计帖子数
     */
    @Select("SELECT COUNT(*) FROM posts WHERE status = 'published' AND category = #{category}")
    int countByCategory(@Param("category") String category);

    /**
     * 根据用户统计帖子数
     */
    @Select("SELECT COUNT(*) FROM posts WHERE user_id = #{userId} AND status = 'published'")
    int countByUserId(@Param("userId") Integer userId);
}
